package cz.matasek.dbLogger.gui;

import cz.matasek.dbLogger.Constant;
import cz.matasek.dbLogger.Settings;
import cz.matasek.dbLogger.model.Database;
import cz.matasek.dbLogger.model.DateTime;
import cz.matasek.dbLogger.model.Table;
import cz.matasek.dbLogger.sql.SQLStatement;
import java.io.File;
import java.io.StringReader;
import java.io.StringWriter;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Date;
import javax.swing.JFileChooser;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;

/**
 * This panel allows querying the logging table.
 *
 * @author Luboš Matásek
 */
public class QueryPanel extends javax.swing.JPanel implements Refreshable {

    /**
     * Serial version UID.
     */
    private static final long serialVersionUID = 3615606528166229199L;
    /**
     * The database object.
     */
    private Database database;
    /**
     * List of tables present in the database.
     */
    private StringListModel tablesModel;
    /**
     * List of users present in the database.
     */
    private StringListModel usersModel;
    /**
     * Result set of the query.
     */
    private LoggingQueryResult resultSet;
    /**
     * Current SQL script.
     */
    private String sqlScript;

    /**
     * Creates a new <code>QueryPanel</code>.
     *
     * @param database the database
     */
    public QueryPanel(Database database) {
        this.database = database;
        tablesModel = getTablesListModel();
        usersModel = new StringListModel(database.getUsers());

        initComponents();

        refresh();
    }

    @Override
    public void refresh() {
        if (database.hasLoggingTable()) {
            _search.setEnabled(true);
        } else {
            _search.setEnabled(false);
        }
    }

    /**
     * Returns list of tables present in the database.
     *
     * @return the list of tables
     */
    public StringListModel getTablesListModel() {
        ArrayList<String> tableNames = new ArrayList<String>();
        for (Table table : database.getNonLoggingTables()) {
            tableNames.add(table.getName());
        }

        return new StringListModel(tableNames);
    }

    /**
     * Creates historical table model for the specified result set.
     *
     * @param historicalTableResult the historical result set
     */
    public void showQueryResult(ResultSet historicalTableResult) {
        resultSet = new LoggingQueryResult(historicalTableResult);
        _queryResult.setModel(resultSet);

        TableRowSorter<TableModel> sorter =
                new TableRowSorter<TableModel>(_queryResult.getModel());

        Comparator comparator = new UniversalComparator();
        for (int i = 0; i < _queryResult.getModel().getColumnCount(); i++) {
            sorter.setComparator(i, comparator);
        }

        _queryResult.setRowSorter(sorter);
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jScrollPane3 = new javax.swing.JScrollPane();
        _users = new javax.swing.JList();
        jScrollPane2 = new javax.swing.JScrollPane();
        _operations = new javax.swing.JList();
        jScrollPane1 = new javax.swing.JScrollPane();
        _tables = new javax.swing.JList();
        jPanel4 = new javax.swing.JPanel();
        _dateFrom = new javax.swing.JSpinner();
        _dateTo = new javax.swing.JSpinner();
        _dateFromActive = new javax.swing.JCheckBox();
        _dateToActive = new javax.swing.JCheckBox();
        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        jScrollPane4 = new javax.swing.JScrollPane();
        _queryResult = new javax.swing.JTable();
        _search = new javax.swing.JButton();
        _newUser = new javax.swing.JTextField();
        _addUser = new javax.swing.JButton();
        _addTable = new javax.swing.JButton();
        _newTable = new javax.swing.JTextField();
        _sqlScriptButton = new javax.swing.JButton();
        _xmlExport = new javax.swing.JButton();
        _htmlExport = new javax.swing.JButton();

        jScrollPane3.setBorder(null);

        _users.setBackground(new java.awt.Color(244, 244, 244));
        _users.setBorder(javax.swing.BorderFactory.createTitledBorder("Users"));
        _users.setModel(usersModel);
        jScrollPane3.setViewportView(_users);

        jScrollPane2.setBorder(null);

        _operations.setBackground(new java.awt.Color(244, 244, 244));
        _operations.setBorder(javax.swing.BorderFactory.createTitledBorder("Operations"));
        _operations.setModel(new javax.swing.AbstractListModel() {
            String[] strings = { "Insert", "Update", "Delete" };
            public int getSize() { return strings.length; }
            public Object getElementAt(int i) { return strings[i]; }
        });
        jScrollPane2.setViewportView(_operations);

        jScrollPane1.setBorder(null);

        _tables.setBackground(new java.awt.Color(244, 244, 244));
        _tables.setBorder(javax.swing.BorderFactory.createTitledBorder("Tables"));
        _tables.setModel(tablesModel);
        jScrollPane1.setViewportView(_tables);

        jPanel4.setBorder(javax.swing.BorderFactory.createTitledBorder("Date and Time"));

        _dateFrom.setModel(new javax.swing.SpinnerDateModel());
        _dateFrom.setEditor(new javax.swing.JSpinner.DateEditor(_dateFrom, "yyyy-MM-dd HH:mm:ss"));
        _dateFrom.setEnabled(false);

        _dateTo.setModel(new javax.swing.SpinnerDateModel());
        _dateTo.setEditor(new javax.swing.JSpinner.DateEditor(_dateTo, "yyyy-MM-dd HH:mm:ss"));
        _dateTo.setEnabled(false);

        _dateFromActive.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                _dateFromActiveActionPerformed(evt);
            }
        });

        _dateToActive.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                _dateToActiveActionPerformed(evt);
            }
        });

        jLabel1.setText("From");

        jLabel2.setText("To");

        javax.swing.GroupLayout jPanel4Layout = new javax.swing.GroupLayout(jPanel4);
        jPanel4.setLayout(jPanel4Layout);
        jPanel4Layout.setHorizontalGroup(
            jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel4Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel4Layout.createSequentialGroup()
                        .addComponent(_dateToActive)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jLabel2)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 36, Short.MAX_VALUE)
                        .addComponent(_dateTo, javax.swing.GroupLayout.PREFERRED_SIZE, 148, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel4Layout.createSequentialGroup()
                        .addComponent(_dateFromActive)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jLabel1)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 24, Short.MAX_VALUE)
                        .addComponent(_dateFrom, javax.swing.GroupLayout.PREFERRED_SIZE, 148, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap())
        );
        jPanel4Layout.setVerticalGroup(
            jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel4Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(_dateFrom, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addComponent(jLabel1))
                    .addComponent(_dateFromActive))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(_dateToActive)
                    .addGroup(jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(_dateTo, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addComponent(jLabel2)))
                .addGap(49, 49, 49))
        );

        _queryResult.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {

            },
            new String [] {

            }
        ));
        jScrollPane4.setViewportView(_queryResult);

        _search.setText("Search");
        _search.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                _searchActionPerformed(evt);
            }
        });

        _addUser.setText("Add");
        _addUser.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                _addUserActionPerformed(evt);
            }
        });

        _addTable.setText("Add");
        _addTable.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                _addTableActionPerformed(evt);
            }
        });

        _sqlScriptButton.setText("SQL script");
        _sqlScriptButton.setEnabled(false);
        _sqlScriptButton.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                _sqlScriptButtonActionPerformed(evt);
            }
        });

        _xmlExport.setText("XML Export");
        _xmlExport.setEnabled(false);
        _xmlExport.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                _xmlExportActionPerformed(evt);
            }
        });

        _htmlExport.setText("HTML export");
        _htmlExport.setEnabled(false);
        _htmlExport.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                _htmlExportActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane4, javax.swing.GroupLayout.DEFAULT_SIZE, 777, Short.MAX_VALUE)
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jScrollPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 150, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(_newTable, javax.swing.GroupLayout.PREFERRED_SIZE, 94, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(_addTable))
                            .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 150, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(_newUser, javax.swing.GroupLayout.PREFERRED_SIZE, 94, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(_addUser))
                            .addComponent(jScrollPane3, javax.swing.GroupLayout.PREFERRED_SIZE, 150, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(layout.createSequentialGroup()
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                    .addComponent(jPanel4, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                                    .addGroup(layout.createSequentialGroup()
                                        .addComponent(_search)
                                        .addGap(42, 42, 42)
                                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                            .addComponent(_htmlExport)
                                            .addComponent(_xmlExport)))))
                            .addGroup(layout.createSequentialGroup()
                                .addGap(5, 5, 5)
                                .addComponent(_sqlScriptButton)))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 62, Short.MAX_VALUE)))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jScrollPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 173, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 173, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(jScrollPane3, javax.swing.GroupLayout.PREFERRED_SIZE, 173, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(_newTable, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(_addTable)
                            .addComponent(_newUser, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(_addUser)))
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jPanel4, javax.swing.GroupLayout.PREFERRED_SIZE, 94, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(18, 18, 18)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(_search)
                            .addComponent(_xmlExport))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(_sqlScriptButton)
                            .addComponent(_htmlExport))))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jScrollPane4, javax.swing.GroupLayout.DEFAULT_SIZE, 334, Short.MAX_VALUE)
                .addContainerGap())
        );
    }// </editor-fold>//GEN-END:initComponents

    /**
     * Enables the "from" date and time check box.
     *
     * @param evt action event
     */
    private void _dateFromActiveActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event__dateFromActiveActionPerformed
        _dateFrom.setEnabled(_dateFromActive.isSelected());
    }//GEN-LAST:event__dateFromActiveActionPerformed

    /**
     * Enables the "to" date and time check box.
     *
     * @param evt action event
     */
    private void _dateToActiveActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event__dateToActiveActionPerformed
        _dateTo.setEnabled(_dateToActive.isSelected());
    }//GEN-LAST:event__dateToActiveActionPerformed

    /**
     * Searches the logging table.
     *
     * @param evt action event
     */
    private void _searchActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event__searchActionPerformed
        DateTime dateTimeFrom = null;
        if (this._dateFromActive.isSelected()) {
            dateTimeFrom = new DateTime((Date) _dateFrom.getValue());
        }

        DateTime dateTimeTo = null;
        if (this._dateToActive.isSelected()) {
            dateTimeTo = new DateTime((Date) _dateTo.getValue());
        }

        ResultSet queryResult =
                database.getConnection().getLoggingTableContent(
                _tables.getSelectedValues(), _operations.getSelectedValues(),
                _users.getSelectedValues(), dateTimeFrom, dateTimeTo);

        showQueryResult(queryResult);


        sqlScript = SQLStatement.getLastSQL();
        _sqlScriptButton.setEnabled(true);
        _xmlExport.setEnabled(true);
        _htmlExport.setEnabled(true);
}//GEN-LAST:event__searchActionPerformed

    /**
     * Adds a new user into the list of users.
     *
     * @param evt action event
     */
    private void _addUserActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event__addUserActionPerformed
        usersModel = new StringListModel(usersModel.getElements());
        usersModel.addElement(_newUser.getText());
        _users.setModel(usersModel);
}//GEN-LAST:event__addUserActionPerformed

    /**
     * Adds a new table into the list of tables.
     *
     * @param evt action event
     */
    private void _addTableActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event__addTableActionPerformed
        tablesModel = new StringListModel(tablesModel.getElements());
        tablesModel.addElement(_newTable.getText());
        _tables.setModel(tablesModel);
}//GEN-LAST:event__addTableActionPerformed

    /**
     * Shows the last performed SQL statement.
     *
     * @param evt action event
     */
    private void _sqlScriptButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event__sqlScriptButtonActionPerformed
        Dialog.SQLQuery(sqlScript);
}//GEN-LAST:event__sqlScriptButtonActionPerformed

    /**
     * Exports the XML document containing rows from the logging table.
     *
     * @param evt action event
     */
    private void _xmlExportActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event__xmlExportActionPerformed
        try {
            JFileChooser fc = new JFileChooser();
            if (fc.showSaveDialog(Gui.getInstance()) == JFileChooser.APPROVE_OPTION) {
                File xmlTarget = fc.getSelectedFile();

                Transformer trans = TransformerFactory.newInstance().newTransformer();
                trans.setOutputProperty(OutputKeys.INDENT, "yes");

                StringWriter xml = new StringWriter();
                StreamResult result = new StreamResult(xml);
                DOMSource source = new DOMSource(resultSet.getXML());
                trans.transform(source, result);

                String xmlString =
                        xml.toString();//.replaceAll("&lt;", Constant.XML_ELEMENT_START_TAG).replaceAll("&gt;", Constant.XML_ELEMENT_END_TAG);

                trans.transform(new StreamSource(new StringReader(xmlString)), new StreamResult(xmlTarget));
            }
        } catch (TransformerException ex) {
        }
    }//GEN-LAST:event__xmlExportActionPerformed

    /**
     * Exports the HTML file containing rows from the logging table.
     *
     * @param evt action event
     */
    private void _htmlExportActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event__htmlExportActionPerformed
        JFileChooser fc = new JFileChooser();
        if (fc.showSaveDialog(Gui.getInstance()) != JFileChooser.APPROVE_OPTION) {
            return;
        }

        try {
            File xmlFile = File.createTempFile("logger", null);

            Transformer xmlTransformer =
                    TransformerFactory.newInstance().newTransformer();

            DOMSource domSource = new DOMSource(resultSet.getXML());
            StreamResult xmlResult = new StreamResult(xmlFile);
            xmlTransformer.transform(domSource, xmlResult);


            File xsltFile = new File(
                    Settings.get(Settings.Key.XSL_DIRECTORY) + File.separator +
                    Settings.get(Settings.Key.XSL_QUERY));
            File htmlFile = fc.getSelectedFile();

            Transformer htmlTransformer =
                    TransformerFactory.newInstance().newTransformer(
                    new StreamSource(xsltFile));
            xmlTransformer.setOutputProperty(OutputKeys.INDENT, "yes");

            StreamSource xmlSource = new StreamSource(xmlFile);
            StreamResult htmlResult = new StreamResult(htmlFile);
            htmlTransformer.transform(xmlSource, htmlResult);
        } catch (Exception ex) {
            Dialog.error(ex.getMessage());
        }
    }//GEN-LAST:event__htmlExportActionPerformed

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton _addTable;
    private javax.swing.JButton _addUser;
    private javax.swing.JSpinner _dateFrom;
    private javax.swing.JCheckBox _dateFromActive;
    private javax.swing.JSpinner _dateTo;
    private javax.swing.JCheckBox _dateToActive;
    private javax.swing.JButton _htmlExport;
    private javax.swing.JTextField _newTable;
    private javax.swing.JTextField _newUser;
    private javax.swing.JList _operations;
    private javax.swing.JTable _queryResult;
    private javax.swing.JButton _search;
    private javax.swing.JButton _sqlScriptButton;
    private javax.swing.JList _tables;
    private javax.swing.JList _users;
    private javax.swing.JButton _xmlExport;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JPanel jPanel4;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JScrollPane jScrollPane3;
    private javax.swing.JScrollPane jScrollPane4;
    // End of variables declaration//GEN-END:variables
}
